<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<%@ page language="java" import="java.sql.*"%>

<%!public String getGradeType(String s) {
		String ret="";
		if (s.equals("gr")) return "Graded";
		if (s.equals("gs")) return "Graded or Satisfactory/Unsatisfactory";
		if (s.equals("su")) return "Satisfactory/Unsatisfactory";

		return ret;
	}

	public String getStateResident(String s) {
		String ret="";
		if (s.equals("c")) return "California Resident";
		if (s.equals("f")) return "Foreign Student";
		if (s.equals("n")) return "Non-CA US Resident";

		return ret;
	}%>
<%
	Class.forName("org.postgresql.Driver");
	Connection conn = DriverManager.getConnection(
			"jdbc:postgresql://localhost:5432/cse132b", "cse132blogin",
			"1234567890");
	Statement stmt;
	int ID;

	ID = Integer.valueOf(request.getParameter("ID"));

	stmt = conn.createStatement();
	ResultSet rs = stmt
			.executeQuery("SELECT students.*, enroll.*, classes.*, courses.*  FROM students , enroll, classes, courses WHERE students.student_id = enroll.student_id AND classes.class_id = enroll.section_id AND courses.course_id = classes.course_id AND enroll.section_id="+ String.valueOf(ID));

	String HTML = "";
	while (rs.next()) {

		HTML += "<tr>";
		HTML += "	<td>" + rs.getString("first_name") + "</td>";
		HTML += "	<td>" + rs.getString("middle_name") + "</td>";
		HTML += "	<td>" + rs.getString("last_name") + "</td>";
		HTML += "	<td nowrap>" + rs.getString("ssn") + "</td>";
		HTML += "	<td nowrap>" + getStateResident(rs.getString("state_resident")) + "</td>";
		HTML += "	<td>" + rs.getString("section_id") + "</td>";
		HTML += "	<td>" + rs.getString("min_units") + "</td>";
		HTML += "	<td>" + rs.getString("max_units") + "</td>";
		if (rs.getBoolean("requires_lab"))
			HTML += "	<td>Yes</td>";
		else
			HTML += "	<td>No</td>";
		HTML += "	<td>" + getGradeType(rs.getString("grade_type")) + "</td>";
	}
	
	
	rs.close();
	stmt.close();
%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
	<table border="1">
		<tr>
			<td valign="top" width="30%">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="/menu.html" />
			</td>
			<td>Display the roster of class<br>
				<table border="1">
					<tr>
						<th>First Name</th>
						<th>Middle Name</th>
						<th>Last Name</th>
						<th>SSN</th>
						<th>State Resdent</th>
						<th>Section ID</th>
						<th>Min Units</th>
						<th>Max Units</th>
						<th>Lab</th>
						<th>Grade Type</th>
					</tr>
					<%=HTML%>
				</table>

			</td>
		</tr>
	</table>
</body>
<%
	// Close the Connection
	conn.close();
%>